package com.hnkywl.sts.dao.base;

import com.hnkywl.sts.entity.sys.Bm;
import com.hnkywl.sts.util.StringFormat;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.hibernate.SQLQuery;
import org.springframework.orm.hibernate3.SessionFactoryUtils;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Date;
import java.util.List;

/**
 * 统一生成编码的类
 * User: Administrator
 * Date: 13-4-17
 * Time: 下午3:17
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class BmDao extends SimpleHibernateTemplate<Bm, Long> {
    private static final String PACKAGE = "BB_GYLWL_XTBM";
    //一级物资编码
    private static final String YJWZBM = PACKAGE + ".USP_BB_GYLWL_XTBM_SCYJWZBM";
    //二级物资编码
    private static final String EJWZBM = PACKAGE + ".USP_BB_GYLWL_XTBM_SCEJWZBM";
    //品名物资编码
    private static final String PMWZBM = PACKAGE + ".USP_BB_GYLWL_XTBM_SCPMWZBM";
    //规格物资编码
    private static final String GGWZBM = PACKAGE + ".USP_BB_GYLWL_XTBM_SCGGWZBM";
    //机构编码
    private static final String JGBM = PACKAGE + ".USP_BB_GYLWL_XTBM_SCJGBM";
    //客户编码
    private static final String KHBM = PACKAGE + ".USP_BB_GYLWL_XTBM_SCKHBM";
    //仓库编码
    private static final String CKBM = PACKAGE + ".USP_BB_GYLWL_XTBM_SCCKBM";
    //协议编码
    private static final String XYBM = PACKAGE + ".USP_BB_GYLWL_XTBM_SCXYBM";
    //物资计划编码
    private static final String WZJHBM = PACKAGE + ".USP_BB_GYLWL_XTBM_SCWZJHBM";
    //资金计划编码
    private static final String ZJJHBM = PACKAGE + ".USP_BB_GYLWL_XTBM_SCZJJHBM";
    //合同编码
    private static final String HTBM = PACKAGE + ".USP_BB_GYLWL_XTBM_SCHTBM";
    //采购单编码
    private static final String CGDBM = PACKAGE + ".USP_BB_GYLWL_XTBM_SCCGDBM";
    //销售单编码
    private static final String XSDBM = PACKAGE + ".USP_BB_GYLWL_XTBM_SCXSDBM";
    //电子借款单编码
    private static final String JKDBM = PACKAGE + ".USP_BB_GYLWL_XTBM_SCJKDBM";
    //结算单编码
    private static final String JSDBM = PACKAGE + ".USP_BB_GYLWL_XTBM_SCJSDBM";
    //收付款单编码
    private static final String SFDBM = PACKAGE + ".USP_BB_GYLWL_XTBM_SCSFDBM";
    //出入库单编码
    private static final String CRKDBM = PACKAGE + ".USP_BB_GYLWL_XTBM_SCCRKDBM";
    //配送单编码
    private static final String PSDBM = PACKAGE + ".USP_BB_GYLWL_XTBM_SCPSDBM";
    //库存卡号
    private static final String KCKHBM = PACKAGE + ".USP_BB_GYLWL_XTBM_KCKHBM";
    //内部领料编码
    private static final String LLDBM = PACKAGE + ".USP_BB_GYLWL_XTBM_LLDBM";
    //转库单号
    private static final String ZKDH = PACKAGE + ".USP_BB_GYLWL_XTBM_ZK";
    //回款计划编码
    private static final String HKJH = PACKAGE + ".USP_BB_GYLWL_XTBM_HKJH";
    //库存盘点
    private static final String KCPD = PACKAGE + ".USP_BB_GYLWL_XTBM_KCPD";
    //二级账户
    private static final String EJZH = PACKAGE + ".USP_BB_GYLWL_XTBM_EJZH";

    /**
     * 一级物资编码
     *
     * @return
     */
    public Bm getYjwzbm() throws SQLException {
        CallableStatement callableStatement = getCallable(StringFormat.format("{call {0}(?)}", YJWZBM));
        return getBm(callableStatement);
    }

    /**
     * 二级物资编码
     *
     * @param yjwzbm 一级物资编码
     * @return 二级物资编码
     */
    public Bm getEjwzbm(String yjwzbm) throws SQLException {
        CallableStatement callableStatement = getCallable(StringFormat.format("{call {0}(?,?)}", EJWZBM));
        callableStatement.setString(2, yjwzbm);
        return getBm(callableStatement);
    }

    /**
     * 品名物资编码
     *
     * @param ejwzbm 二级物资编码
     * @return 品名物资编码
     */
    public Bm getPmwzbm(String ejwzbm) throws SQLException {
        CallableStatement callableStatement = getCallable(StringFormat.format("{call {0}(?,?)}", PMWZBM));
        callableStatement.setString(2, ejwzbm);
        return getBm(callableStatement);
    }

    /**
     * 规格物资编码
     *
     * @param ppwzbm 品名物资编码
     * @return 规格物资编码
     */
    public Bm getGgwzbm(String ppwzbm) throws SQLException {
        CallableStatement callableStatement = getCallable(StringFormat.format("{call {0}(?,?)}", GGWZBM));
        callableStatement.setString(2, ppwzbm);
        return getBm(callableStatement);
    }

    /**
     * 机构编码
     *
     * @return 机构编码
     */
    public Bm getJgbm() throws SQLException {
        CallableStatement callableStatement = getCallable(StringFormat.format("{call {0}(?)}", JGBM));
        return getBm(callableStatement);
    }

    /**
     * 客户编码
     *
     * @return 客户编码
     */
    public Bm getKhbm() throws SQLException {
        CallableStatement callableStatement = getCallable(StringFormat.format("{call {0}(?)}", KHBM));
        return getBm(callableStatement);
    }

    /**
     * 仓库编码
     *
     * @param ssgsbm
     * @param ssjgid
     * @return
     * @throws SQLException
     */
    public Bm getCkbm(String ssgsbm, Long ssjgid) throws SQLException {
        CallableStatement callableStatement = getCallable(StringFormat.format("{call {0}(?,?,?)}", CKBM));
        callableStatement.setString(2, ssgsbm);
        callableStatement.setLong(3, ssjgid);
        return getBm(callableStatement);
    }


    /**
     * 得到协议编码
     *
     * @param xylx   协议类型，采购协议：1，销售协议：2，三方协议：3
     * @param ssgsbm
     * @param ssjgid
     * @return 协议编码
     * @throws SQLException
     */
    public Bm getXybm(String xylx, String ssgsbm, Long ssjgid) throws SQLException {
        CallableStatement callableStatement = getCallable(StringFormat.format("{call {0}(?,?,?,?)}", XYBM));
        callableStatement.setString(2, xylx);
        callableStatement.setString(3, ssgsbm);
        callableStatement.setLong(4, ssjgid);
        return getBm(callableStatement);
    }

    /**
     * 物资计划编码
     *
     * @param lx     类型:ZGWZJG 自购物资计划  TPWZJG 统配物资计划
     * @param ssgsbm 机构编码
     * @param ssjgid 机构id
     * @return 物资计划编码
     */
    public Bm getWzjhbm(String lx, String ssgsbm, Long ssjgid) throws SQLException {
        CallableStatement callableStatement = getCallable(StringFormat.format("{call {0}(?,?,?,?)}", WZJHBM));
        callableStatement.setString(2, lx);
        callableStatement.setString(3, ssgsbm);
        callableStatement.setLong(4, ssjgid);
        return getBm(callableStatement);
    }

    /**
     * 资金计划编码
     *
     * @param ssgsbm 机构编码
     * @param ssjgid 机构id
     * @return 资金计划编码
     */
    public Bm getZjjhbm(String lx, String ssgsbm, Long ssjgid) throws SQLException {
        CallableStatement callableStatement = getCallable(StringFormat.format("{call {0}(?,?,?,?)}", ZJJHBM));
        callableStatement.setString(2, lx);
        callableStatement.setString(3, ssgsbm);
        callableStatement.setLong(4, ssjgid);
        return getBm(callableStatement);
    }

    /**
     * 生成合同编码
     *
     * @param htlx   合同类型，库存销售：1，直达销售：2，采购入库：3，采购直达：4,保供合同：5
     * @param ssgsbm 机构编码
     * @param ssjgid 机构id
     * @return 生成合同编码
     */
    public Bm getHtbm(String htlx, String ssgsbm, Long ssjgid) throws SQLException {
        CallableStatement callableStatement = getCallable(StringFormat.format("{call {0}(?,?,?,?)}", HTBM));
        callableStatement.setString(2, htlx);
        callableStatement.setString(3, ssgsbm);
        callableStatement.setLong(4, ssjgid);
        return getBm(callableStatement);
    }

    /**
     * 采购单编码
     *
     * @param cgdlx  --采购单类型，库存销售：1，直达销售：2
     * @param cghtbm 采购合同编码
     * @param ssjgid 机构Id
     * @return 采购单编码
     */
    public Bm getCgdbm(String cgdlx, String cghtbm, Long ssjgid) throws SQLException {
        CallableStatement callableStatement = getCallable(StringFormat.format("{call {0}(?,?,?,?)}", CGDBM));
        callableStatement.setString(2, cgdlx);
        callableStatement.setString(3, cghtbm);
        callableStatement.setLong(4, ssjgid);
        return getBm(callableStatement);
    }

    /**
     * 销售单编码
     *
     * @param xsdlx  --销售单类型，库存销售：1，直达销售：2
     * @param xshtbm 销售合同编码
     * @param ssjgid 机构Id
     * @return 销售单编码
     */
    public Bm getXsdbm(String xsdlx, String xshtbm, Long ssjgid) throws SQLException {
        CallableStatement callableStatement = getCallable(StringFormat.format("{call {0}(?,?,?,?)}", XSDBM));
        callableStatement.setString(2, xsdlx);
        callableStatement.setString(3, xshtbm);
        callableStatement.setLong(4, ssjgid);
        return getBm(callableStatement);
    }

    /**
     * 电子借款单编码
     *
     * @param zjjhbm 资金计划编码
     * @param ssjgid 机构ID
     * @return 电子借款单编码
     */
    public Bm getJkdbm(String zjjhbm, Long ssjgid) throws SQLException {
        CallableStatement callableStatement = getCallable(StringFormat.format("{call {0}(?,?,?)}", JKDBM));
        callableStatement.setString(2, zjjhbm);
        callableStatement.setLong(3, ssjgid);
        return getBm(callableStatement);
    }

    /**
     * 结算单编码
     *
     * @param jsdlx  结算单类型，采购结算单：1，销售结算单：2
     * @param ssjgid 机构id
     * @return 结算单编码
     */
    public Bm getJsdbm(String jsdlx, Long ssjgid) throws SQLException {
        CallableStatement callableStatement = getCallable(StringFormat.format("{call {0}(?,?,?)}", JSDBM));
        callableStatement.setString(2, jsdlx);
        callableStatement.setLong(3, ssjgid);
        return getBm(callableStatement);
    }

    /**
     * 收付款单编码
     *
     * @param lx     收付款单类型，收款单：1，付款单：2
     * @param ssjgid 机构编码
     * @return 收付款单编码
     */
    public Bm getSfdbm(String lx, Long ssjgid) throws SQLException {
        CallableStatement callableStatement = getCallable(StringFormat.format("{call {0}(?,?,?)}", SFDBM));
        callableStatement.setString(2, lx);
        callableStatement.setLong(3, ssjgid);
        return getBm(callableStatement);
    }

    /**
     * 出入库单编码
     *
     * @param lx     收付款单类型，出库单：1，入库单：2
     * @param ssjgid 机构编码
     * @return 出入库单编码
     */
    public Bm getCrkdbm(String lx, Long ssjgid) throws SQLException {
        CallableStatement callableStatement = getCallable(StringFormat.format("{call {0}(?,?,?)}", CRKDBM));
        callableStatement.setString(2, lx);
        callableStatement.setLong(3, ssjgid);
        return getBm(callableStatement);
    }

    /**
     * 配送单编码
     *
     * @param ssjgid 机构编码
     * @return 配送单编码
     */
    public Bm getPsdbm(Long ssjgid) throws SQLException {
        CallableStatement callableStatement = getCallable(StringFormat.format("{call {0}(?,?)}", PSDBM));
        callableStatement.setLong(2, ssjgid);
        return getBm(callableStatement);
    }

    /**
     * 库存卡号编码
     *
     * @param ckid 仓储编码
     * @return 库存卡号编码
     */
    public Bm getKckhbm(Long ckid) throws SQLException {
        CallableStatement callableStatement = getCallable(StringFormat.format("{call {0}(?,?)}", KCKHBM));
        callableStatement.setLong(2, ckid);
        return getBm(callableStatement);
    }

    /**
     * 内部领料编码
     *
     * @return
     * @throws SQLException
     */
    public Bm getLldbm() throws SQLException {
        CallableStatement callableStatement = getCallable(StringFormat.format("{call {0}(?)}", LLDBM));
        return getBm(callableStatement);
    }

    /**
     * 转库单号
     *
     * @return
     * @throws SQLException
     */
    public Bm getZkdhbm() throws SQLException {
        CallableStatement callableStatement = getCallable(StringFormat.format("{call {0}(?)}", ZKDH));
        return getBm(callableStatement);
    }

    /**
     * 回款计划
     *
     * @return
     * @throws SQLException
     */
    public Bm getHkjhbm() throws SQLException {
        CallableStatement callableStatement = getCallable(StringFormat.format("{call {0}(?)}", HKJH));
        return getBm(callableStatement);
    }

    /**
     * 库存盘点
     *
     * @return
     * @throws SQLException
     */
    public Bm getKcpdbm() throws SQLException {
        CallableStatement callableStatement = getCallable(StringFormat.format("{call {0}(?)}", KCPD));
        return getBm(callableStatement);
    }

    /**
     * 二级账户编码
     *
     * @return
     * @throws SQLException
     */
    public Bm getEjzhbm() throws SQLException {
        CallableStatement callableStatement = getCallable(StringFormat.format("{call {0}(?)}", EJZH));
        return getBm(callableStatement);
    }

    private Bm findById(Long id) {
        return this.get(id);
    }

    private CallableStatement getCallable(String fn) throws SQLException {
        return SessionFactoryUtils.getDataSource(getSessionFactory()).getConnection().prepareCall(fn);
    }

    private Bm getBm(CallableStatement callableStatement) throws SQLException {
        try{
            String bmstr = "";
            callableStatement.registerOutParameter(1, Types.VARCHAR);
            if (callableStatement.executeUpdate() != 0) {
                bmstr = callableStatement.getString(1);
            }
            String[] bms = bmstr.split(",");
            return this.get(Long.valueOf(bms[0]));
        } catch (Exception e){
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {
            if (callableStatement != null) {
                try {
                    callableStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e.getMessage());
                }
            }
            if (callableStatement.getConnection() != null) {
                try {
                    callableStatement.getConnection().close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e.getMessage());
                }
            }
        }
    }

    public Bm findByBmz(String bmz) {
        return findUniqueByProperty("bmz", bmz);
    }

    public Bm findByCodeId(String codeId, String type) {
        String sql = "select bm.id from t_sys_bmb bm left join t_sys_bmlx bmlx on(bm.bmlxid = bmlx.id) " +
                "where bm.zt = 1 and bm.bmylz1 = '" + codeId + "' and lower(bmlx.lxzjm) = '" + type + "'";
        SQLQuery sqlQuery = createSQLQuery(sql);
        BigDecimal id = (BigDecimal) sqlQuery.uniqueResult();
        if(id == null){
            return  null;
        } else {
            return get(id.longValue());
        }
    }
}
